﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BuildSDFDatabase
{
	class IntermediateDataBuilder : IDisposable
	{
		private const double TFIDFLogBase = 2;
		private const int RemoveDocSizeThreshold = 0;
		private DateTime StartTime, EndTime;
		private DatabaseAccessor dbAccessor;
		private DataTable displayTreeDT, corpusTreeNodeDT, treeNodeKeywordDT, treeNodeKeywordTFIDFDT;
		public IntermediateDataBuilder(DatabaseAccessor dbAccessor)
		{
			this.dbAccessor = dbAccessor;

			SqlCeResultSet res = dbAccessor.DBExecuteQuery("select StartTime from DataTime;", ResultSetOptions.None);
			if (res.Read())
				StartTime = res.GetDateTime(0);
			res = dbAccessor.DBExecuteQuery("select EndTime from DataTime;", ResultSetOptions.None);
			if (res.Read())
				EndTime = res.GetDateTime(0);

			corpusTreeNodeDT = new DataTable("CorpusTreeNode");
			corpusTreeNodeDT.Columns.Add(new DataColumn("NodeID", typeof(int)));
			corpusTreeNodeDT.Columns.Add(new DataColumn("IsBundle", typeof(bool)));
			corpusTreeNodeDT.Columns.Add(new DataColumn("CorpusID", typeof(int)));
			corpusTreeNodeDT.Columns.Add(new DataColumn("Size", typeof(int)));
			corpusTreeNodeDT.Columns.Add(new DataColumn("StartTime", typeof(DateTime)));
			corpusTreeNodeDT.Columns.Add(new DataColumn("EndTime", typeof(DateTime)));
			corpusTreeNodeDT.Columns.Add(new DataColumn("LeadingFactor", typeof(float)));

			displayTreeDT = new DataTable("DisplayTree");
			displayTreeDT.Columns.Add(new DataColumn("CorpusID", typeof(int)));
			displayTreeDT.Columns.Add(new DataColumn("ChildNodeId", typeof(int)));
			displayTreeDT.Columns.Add(new DataColumn("ChildIsBundle", typeof(bool)));
			displayTreeDT.Columns.Add(new DataColumn("ParentNodeId", typeof(int)));

			treeNodeKeywordDT = new DataTable("CorpusTreeNodeKeyword");
			treeNodeKeywordDT.Columns.Add(new DataColumn("NodeID", typeof(int)));
			treeNodeKeywordDT.Columns.Add(new DataColumn("IsBundle", typeof(bool)));
			treeNodeKeywordDT.Columns.Add(new DataColumn("CorpusID", typeof(int)));
			treeNodeKeywordDT.Columns.Add(new DataColumn("Keyword", typeof(string)));
			treeNodeKeywordDT.Columns.Add(new DataColumn("Frequency", typeof(int)));

			treeNodeKeywordTFIDFDT = new DataTable("CorpusTreeNodeKeywordTFIDF");
			treeNodeKeywordTFIDFDT.Columns.Add(new DataColumn("NodeID", typeof(int)));
			treeNodeKeywordTFIDFDT.Columns.Add(new DataColumn("IsBundle", typeof(bool)));
			treeNodeKeywordTFIDFDT.Columns.Add(new DataColumn("CorpusID", typeof(int)));
			treeNodeKeywordTFIDFDT.Columns.Add(new DataColumn("Keyword", typeof(string)));
			treeNodeKeywordTFIDFDT.Columns.Add(new DataColumn("Value", typeof(double)));

		}

		public void BuildDisplayTree()
		{
			var res = dbAccessor.DBExecuteQuery("select Sum(Size) from CorpusTreeNode;", ResultSetOptions.None);
			int[] corporaId = GetCorporaID();
			int[] fieldsId = GetFieldsID();

			foreach (var corpusId in corporaId)
			{
				foreach (var fieldId in fieldsId)
				{
					Dictionary<int, int> topicSize = GetTopicSize(corpusId, fieldId);
					TopicInsertCorpusTreeNode(corpusId, topicSize);
					InsertCorpusTreeNodeKeyword(corpusId, fieldId, topicSize.Keys.ToList());

					Dictionary<int, List<int>> bundleTopics = GetBundleTopics(fieldId, topicSize);
					BundleInsertCorpusTreeNode(corpusId, bundleTopics, topicSize);
					InsertDisplayTree(corpusId, bundleTopics);
				}
			}
		}

		#region TFIDF
		public void BuildKeywordTFIDF()
		{
			int[] corporaId = GetCorporaID();

			foreach (var corpusId in corporaId)
			{
				List<Tuple<int, int>> level0Bundles = new List<Tuple<int, int>>();
				List<Tuple<int, int>> level1Bundles = new List<Tuple<int, int>>();
				List<Tuple<int, int>> topics = new List<Tuple<int, int>>();
				var query = string.Format(@"select NodeID,IsBundle,CorpusID from CorpusTreeNode
											where CorpusID={0};", corpusId);
				var res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
				while (res.Read())
				{
					bool isBundle = res.GetBoolean(1);
					if (!isBundle)
						topics.Add(new Tuple<int, int>(res.GetInt32(0), res.GetInt32(2)));
					else
					{
						int nodeId = res.GetInt32(0);
						if ((nodeId & 0x00FFFFFF) == 0)
							level0Bundles.Add(new Tuple<int, int>(nodeId, res.GetInt32(2)));
						else
							level1Bundles.Add(new Tuple<int, int>(nodeId, res.GetInt32(2)));
					}
				}

				BuildLevelKeywordTFIDF(level0Bundles, true);
				BuildLevelKeywordTFIDF(level1Bundles, true);
				BuildLevelKeywordTFIDF(topics, false);
			}
		}

		private void BuildLevelKeywordTFIDF(List<Tuple<int, int>> levelTopics, bool isBundle)
		{
			treeNodeKeywordTFIDFDT.Rows.Clear();
			List<List<Keyword>> topicTFKeywords = new List<List<Keyword>>();
			var globalKeywordDict = BuildLevelKeywordTF(levelTopics, isBundle, topicTFKeywords);
		
			for (int i = 0; i < levelTopics.Count; i++)
			{
				var topicId = levelTopics[i].Item1;
				var corpusId = levelTopics[i].Item2;
				var tfKeywords = topicTFKeywords[i];
				int topicAllKeywordCount = (int)tfKeywords.Select(t => t.Rank).Sum();
				int topicCount = levelTopics.Count;

				foreach (var tfWord in tfKeywords)
				{
					double tf = tfWord.Rank * 1.0 / topicAllKeywordCount;
					int containKeywordTopicCount = topicTFKeywords.Select(t => t).Where(t => KeywordsContainKeyword(t, tfWord.Content) == true).Count();
					double idf = Math.Log(topicCount * 1.0 / containKeywordTopicCount, TFIDFLogBase);
					double tfidf = tf * idf;
					treeNodeKeywordTFIDFDT.Rows.Add(topicId, isBundle, corpusId, tfWord.Content, tfidf);
				}
			}
			dbAccessor.BulkInsert(treeNodeKeywordTFIDFDT);
		}

		private bool KeywordsContainKeyword(List<Keyword> keywords, string keyword)
		{
			foreach (var word in keywords)
			{
				if (word.Content == keyword)
					return true;
			}
			return false;
		}

		private Dictionary<string, int> BuildLevelKeywordTF(List<Tuple<int, int>> levelTopics, bool isBundle,
			List<List<Keyword>> topicTFKeywords)
		{
			topicTFKeywords.Clear();
			if (!isBundle)
			{
				foreach (var topic in levelTopics)
					topicTFKeywords.Add(GetTopicTFKeywords(topic.Item1, topic.Item2));
			}
			else
			{
				foreach (var bundle in levelTopics)
				{
					var bundleId = bundle.Item1 & 0x00FFFFFF;
					var corpusId = bundle.Item2;
					List<int> leavesId = new List<int>();
					if (bundleId == 0)
					{
						var childBundle = GetChildrenFromTree(corpusId, bundle.Item1).Select(t => t.Item1);
						foreach (var bid in childBundle)
							leavesId.AddRange(GetChildrenFromTree(corpusId, bid).Select(t => t.Item1).ToList());
					}
					else
						leavesId.AddRange(GetChildrenFromTree(corpusId, bundleId).Select(t => t.Item1).ToList());

					Dictionary<string, int> tfKeywordsDict = new Dictionary<string, int>();
					foreach (var topicId in leavesId)
					{
						List<Keyword> leafKeywords = GetTopicTFKeywords(topicId, corpusId);
						foreach (var word in leafKeywords)
						{
							if (!tfKeywordsDict.ContainsKey(word.Content))
								tfKeywordsDict.Add(word.Content, (int)word.Rank);
							else
								tfKeywordsDict[word.Content] += (int)word.Rank;
						}
					}

					List<Keyword> tfKeywords = new List<Keyword>();
					foreach (var word in tfKeywordsDict)
						tfKeywords.Add(new Keyword(word.Key, word.Value));
					topicTFKeywords.Add(tfKeywords);
				}
			}
			Dictionary<string, int> keywordDict = new Dictionary<string,int>();
			foreach (var topicWord in topicTFKeywords)
			{
				foreach (var word in topicWord)
				{
					if(!keywordDict.ContainsKey(word.Content))
						keywordDict.Add(word.Content, (int)word.Rank);
					else
						keywordDict[word.Content] += (int)word.Rank;
				}
			}
			return keywordDict;
		}

		private List<Keyword> GetTopicTFKeywords(int topicId, int corpusId)
		{
			var query = string.Format(@"select Keyword,Frequency from CorpusTreeNodeKeyword
							where NodeID={0} and IsBundle=0 and CorpusID={1} order by Frequency desc",
								topicId, corpusId);
			var res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			List<Keyword> words = new List<Keyword>();
			while (res.Read())
				words.Add(new Keyword(res.GetString(0), res.GetInt32(1)));
			return words;
		}

		public Tuple<int, bool>[] GetChildrenFromTree(int corpusId, int parentId)
		{
			List<Tuple<int, bool>> children = new List<Tuple<int, bool>>();
			var query = string.Format(@"select * from DisplayTree
						where CorpusID={0} and ParentNodeId={1}",
						corpusId, parentId);
			var res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			while (res.Read())
			{
				var childId = res.GetInt32(1);
				bool isChildBundle = res.GetBoolean(2);
				children.Add(new Tuple<int, bool>(childId, isChildBundle));
			}
			return children.ToArray();
		}
		#endregion

		#region TreeNode

		private void InsertDisplayTree(int corpusId, Dictionary<int, List<int>> bundleTopics)
		{
			displayTreeDT.Rows.Clear();

			int bundle0 = 0;
			List<int> bundles = new List<int>();
			foreach (var key in bundleTopics.Keys)
			{
				if ((key & 0x00FFFFFF) == 0)
					bundle0 = key;
				else
					bundles.Add(key);
			}
			foreach (var bundle in bundles)
			{
				displayTreeDT.Rows.Add(corpusId, bundle, true, bundle0);
				foreach(var topic in bundleTopics[bundle])
					displayTreeDT.Rows.Add(corpusId, topic, false, bundle);
			}
			dbAccessor.BulkInsert(displayTreeDT);
		}

		private int[] GetCorporaID()
		{
			string query = string.Format("select * from Corpora;");
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			List<int> corpora = new List<int>();
			while (res.Read())
				corpora.Add(res.GetInt32(0));
			return corpora.ToArray();
		}
		private int[] GetFieldsID()
		{
			string query = string.Format("select * from Field;");
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			List<int> fields = new List<int>();
			while (res.Read())
				fields.Add(res.GetInt32(0));
			return fields.ToArray();
		}

		private void TopicInsertCorpusTreeNode(int corpusId, Dictionary<int, int> topicSize)
		{
			corpusTreeNodeDT.Rows.Clear();
			foreach (var item in topicSize)
			{
				var topicId = item.Key;
				var leadingFactor = GetNodeLeadingFactor(corpusId, topicId, false);
				DateTime startTime, endTime;
				GetTopicStartEndTime(corpusId, topicId, out startTime, out endTime);
				corpusTreeNodeDT.Rows.Add(topicId, false, corpusId, item.Value, startTime, endTime, leadingFactor);
			}
			dbAccessor.BulkInsert(corpusTreeNodeDT);
		}

		private void BundleInsertCorpusTreeNode(int corpusId,
			Dictionary<int, List<int>> bundleTopics, Dictionary<int, int> topicSize)
		{
			corpusTreeNodeDT.Rows.Clear();
			foreach (var item in bundleTopics)
			{
				var bundleId = item.Key;
				var leadingFactor = GetNodeLeadingFactor(corpusId, bundleId, true);
				var size = 0;
				DateTime startTime = EndTime, endTime = StartTime;
				foreach (var topicId in item.Value)
				{
					size += topicSize[topicId];
					DateTime st, et;
					GetTopicStartEndTime(corpusId, topicId, out st, out et);
					if (st >= StartTime && startTime > st)
						startTime = st;
					if (et <= EndTime && endTime < et)
						endTime = et;
				}
				corpusTreeNodeDT.Rows.Add(bundleId, true, corpusId, size, startTime, endTime, leadingFactor);
			}
			dbAccessor.BulkInsert(corpusTreeNodeDT);
		}

		private Dictionary<int, List<int>> GetBundleTopics(int fieldId, Dictionary<int, int> topicSize)
		{
			Dictionary<int, List<int>> bundles = new Dictionary<int, List<int>>();
			var query = string.Format(@"select Child from RoseTree 
							where FieldID='{0}' and Parent='{1}'", fieldId, "bundle0");
			var res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			while(res.Read())
				bundles.Add(fieldId << 24 | int.Parse(res.GetString(0).Replace("bundle", "")), new List<int>());

			foreach(var topic in topicSize)
			{
				string child = "cluster" + (topic.Key & 0x00FFFFFF);
				string parent = "";
				while (true)
				{
					query = string.Format(@"select Parent from RoseTree 
							where FieldID='{0}' and Child='{1}'", fieldId, child);
					res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
					if (res.Read())
						parent = res.GetString(0);
					else
						throw new Exception("rosetree error.");
					if(parent == "bundle0")
						break;
					child = parent;
				}
				int bundleId = fieldId << 24 | int.Parse(child.Replace("bundle", ""));
				bundles[bundleId].Add(topic.Key);
			}

			List<int> root = new List<int>();
			foreach (var bundle in bundles)
				root.AddRange(bundle.Value);
			bundles.Add(fieldId << 24 | 0, root);
			return bundles;
		}

		private Dictionary<int, int> GetTopicSize(int corpusId, int fieldId)
		{
			string query = string.Format(@"select * from CorpusTimeCluster
												where CorpusID={0} and FieldID={1};", corpusId, fieldId);
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			Dictionary<int, int> topicSize = new Dictionary<int, int>();
			while (res.Read())
			{
				int clusterId = res.GetInt32(1);
				int topicId = fieldId << 24 | clusterId;
				int size = res.GetInt32(4);
				if (!topicSize.ContainsKey(topicId))
					topicSize.Add(topicId, 0);
				topicSize[topicId] += size;
			}
			return topicSize.Select(t=>t).Where(t=>t.Value > RemoveDocSizeThreshold).ToDictionary(t=>t.Key, t=>t.Value);
		}

		private void GetTopicStartEndTime(int corpusId, int topicId,
			out DateTime startTime, out DateTime endTime)
		{
			var fieldId = topicId >> 24;
			var clusterId = topicId & 0x00FFFFFF;

			string query = string.Format(@"select Timestamp from CorpusTimeCluster 
										where CorpusID='{0}' and FieldID='{1}' and ClusterID='{2}';",
										corpusId, fieldId, clusterId);
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);

			startTime = EndTime;
			endTime = StartTime;
			List<DateTime> times = new List<DateTime>();
			while (res.Read())
			{
				DateTime time = res.GetDateTime(0);
				times.Add(time);
				if (time >= StartTime && startTime > time)
					startTime = time;
				if (time <= EndTime && endTime < time)
					endTime = time;
			}
		}

		private double GetNodeLeadingFactor(int corpusId, int nodeId, bool isBundle)
		{
			int fieldId = nodeId >> 24;
			int clusterId = nodeId & 0x00FFFFFF;
			var query = string.Format(@"select LeadLagValue from GlobalLeadLag
										where FieldID='{0}' and CorpusID='{1}' and ObjectID=",
										fieldId, corpusId);
			if(isBundle)
				query += string.Format("'bundle{0}';", clusterId);
			else
				query += string.Format("'topic{0}';", clusterId);
			SqlCeResultSet res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
			if (res.Read())
				return res.GetDouble(0);
			else
				throw new Exception("Corpus GlobalLeadLagValue has not been stored.");
		}
		#endregion

		#region Keyword

		private void InsertCorpusTreeNodeKeyword(int corpusId, int fieldId, List<int> topics)
		{
			treeNodeKeywordDT.Rows.Clear();
			foreach (var topicId in topics)
			{
				Dictionary<string, int> keywords = new Dictionary<string, int>();
				int clusterId = topicId & 0x00FFFFF;
				var query = string.Format(@"select Keyword,Frequency from CorpusTimeClusterKeyword
							where FieldID={0} and ClusterID={1} and CorpusID={2}",
											fieldId, clusterId, corpusId);
				var res = dbAccessor.DBExecuteQuery(query, ResultSetOptions.None);
				while (res.Read())
				{
					var keyword = res.GetString(0);
					var frq = res.GetInt32(1);
					if (!keywords.ContainsKey(keyword))
						keywords.Add(keyword, frq);
					else
						keywords[keyword] += frq;
				}
				foreach (var item in keywords)
				{
					if(item.Value > 0)
						treeNodeKeywordDT.Rows.Add(topicId, false, corpusId, item.Key, item.Value);
				}
			}
			dbAccessor.BulkInsert(treeNodeKeywordDT);
		}

		#endregion

		public void Dispose()
		{
			dbAccessor.Dispose();
		}
	}

	internal struct Keyword
	{
		public string Content { get; set; }
		public double Rank { get; set; }

		public Keyword(string word, double rank)
			: this()
		{
			Content = word;
			Rank = rank;
		}
	}
}
